1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3 Imports System.IO
4
5 Public Class frmPurchaseReport
6 Dim a, b, c As Decimal
7
8 Sub Reset()
9 cmbSupplier.Text = ""
10 dtpDateFrom.Value = Today
11 dtpDateTo.Value = Today
12 End Sub
13 Private Sub btnReset_Click(sender As System.Object, e As System.EventArgs) Handles btnReset.Click
14 Reset()
15 End Sub
16
17 Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
18 Me.Close()
19 End Sub
20
21 Private Sub btnViewReport_Click(sender As System.Object, e As System.EventArgs) Handles btnViewReport.Click
22 Try
23 If Len(Trim(cmbSupplier.Text)) = 0 Then
24 MessageBox.Show("Please select supplier", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
25 cmbSupplier.Focus()
26 Exit Sub
27 End If
28 Cursor = Cursors.WaitCursor
29 Timer1.Enabled = True
30 Dim rpt As New rptPurchase 'The report you created.
31 Dim myConnection As SqlConnection
32 Dim MyCommand As New SqlCommand()
33 Dim myDA As New SqlDataAdapter()
34 Dim myDS As New DataSet 'The DataSet you created.
35 myConnection = New SqlConnection(cs)
36 MyCommand.Connection = myConnection
37 MyCommand.CommandText = "SELECT Distinct Stock.ST_ID, Stock.Stock_ID, Stock.Date, Stock.SupplierID, Stock.GrandTotal, Stock.TotalPayment, Stock.PaymentDue, Stock.Remarks, Stock_Product.SP_ID, Stock_Product.StockID, Stock_Product.ProductID,Stock_Product.Qty, Stock_Product.Price, Stock_Product.TotalAmount, Supplier.ID, Supplier.SupplierID AS Expr1, Supplier.Name, Supplier.Address, Supplier.City, Supplier.State, Supplier.ZipCode,Supplier.ContactNo, Supplier.EmailID, Supplier.Remarks AS Expr2, Product.PID, Product.ProductCode, Product.ProductName, Product.SubCategoryID, Product.Description, Product.CostPrice, Product.SellingPrice,Product.Discount, Product.VAT, Product.ReorderPoint FROM Stock INNER JOIN Stock_Product ON Stock.ST_ID = Stock_Product.StockID INNER JOIN Supplier ON Stock.SupplierID = Supplier.ID INNER JOIN Product ON Stock_Product.ProductID = Product.PID where Supplier.Name=@d1"
38 MyCommand.Parameters.AddWithValue("@d1", cmbSupplier.Text)
39 MyCommand.CommandType = CommandType.Text
40 myDA.SelectCommand = MyCommand
41 myDA.Fill(myDS, "Stock")
42 myDA.Fill(myDS, "Stock_Product")
43 myDA.Fill(myDS, "Product")
44 myDA.Fill(myDS, "Supplier")
45 con = New SqlConnection(cs)
46 con.Open()
47 Dim ct As String = "select ISNULL(sum(GrandTotal),0),ISNULL(sum(TotalPayment),0),ISNULL(sum(PaymentDue),0) from Stock,Supplier where Supplier.ID=Stock.SupplierID and Name=@d1"
48 cmd = New SqlCommand(ct)
49 cmd.Parameters.AddWithValue("@d1", cmbSupplier.Text)
50 cmd.Connection = con
51 rdr = cmd.ExecuteReader()
52 While rdr.Read()
53 a = rdr.GetValue(0)
54 b = rdr.GetValue(1)
55 c = rdr.GetValue(2)
56 End While
57 con.Close()
58 con = New SqlConnection(cs)
59 con.Open()
60 cmd = New SqlCommand("SELECT CONVERT(varchar(10),YEAR(Date)) AS Year, SUM(GrandTotal) AS GrandTotal from Stock,Supplier where Supplier.ID=Stock.SupplierID and Name=@d1 GROUP BY YEAR(Date) ORDER BY Year", con)
61 cmd.Parameters.AddWithValue("@d1", cmbSupplier.Text)
62 adp = New SqlDataAdapter(cmd)
63 dtable = New DataTable()
64 adp.Fill(dtable)
65 con.Close()
66 myDS.Tables.Add(dtable)
67 myDS.WriteXmlSchema("TotalPurchase.xml")
68 rpt.Subreports(0).SetDataSource(myDS)
69 rpt.SetDataSource(myDS)
70 rpt.SetParameterValue("p1", dtpDateFrom.Value.Date)
71 rpt.SetParameterValue("p2", dtpDateTo.Value.Date)
72 rpt.SetParameterValue("p3", a)
73 rpt.SetParameterValue("p4", b)
74 rpt.SetParameterValue("p5", c)
75 rpt.SetParameterValue("p6", Today)
76 frmReport.CrystalReportViewer1.ReportSource = rpt
77 frmReport.ShowDialog()
78 Catch ex As Exception
79 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
80 End Try
81 End Sub
82
83 Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick
84 Cursor = Cursors.Default
85 Timer1.Enabled = False
86 End Sub
87
88 Private Sub btnGetData_Click(sender As System.Object, e As System.EventArgs) Handles btnGetData.Click
89 Try
90 Cursor = Cursors.WaitCursor
91 Timer1.Enabled = True
92 Dim rpt As New rptPurchase 'The report you created.
93 Dim myConnection As SqlConnection
94 Dim MyCommand As New SqlCommand()
95 Dim myDA As New SqlDataAdapter()
96 Dim myDS As New DataSet 'The DataSet you created.
97 myConnection = New SqlConnection(cs)
98 MyCommand.Connection = myConnection
99 MyCommand.CommandText = "SELECT Distinct Stock.ST_ID, Stock.Stock_ID, Stock.Date, Stock.SupplierID, Stock.GrandTotal, Stock.TotalPayment, Stock.PaymentDue, Stock.Remarks, Stock_Product.SP_ID, Stock_Product.StockID, Stock_Product.ProductID,Stock_Product.Qty, Stock_Product.Price, Stock_Product.TotalAmount, Supplier.ID, Supplier.SupplierID AS Expr1, Supplier.Name, Supplier.Address, Supplier.City, Supplier.State, Supplier.ZipCode,Supplier.ContactNo, Supplier.EmailID, Supplier.Remarks AS Expr2, Product.PID, Product.ProductCode, Product.ProductName, Product.SubCategoryID, Product.Description, Product.CostPrice, Product.SellingPrice,Product.Discount, Product.VAT, Product.ReorderPoint FROM Stock INNER JOIN Stock_Product ON Stock.ST_ID = Stock_Product.StockID INNER JOIN Supplier ON Stock.SupplierID = Supplier.ID INNER JOIN Product ON Stock_Product.ProductID = Product.PID where Stock.Date between @d1 and @d2 order by Stock.Date"
100 MyCommand.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
101 MyCommand.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
102 MyCommand.CommandType = CommandType.Text
103 myDA.SelectCommand = MyCommand
104 myDA.Fill(myDS, "Stock")
105 myDA.Fill(myDS, "Stock_Product")
106 myDA.Fill(myDS, "Product")
107 myDA.Fill(myDS, "Supplier")
108 con = New SqlConnection(cs)
109 con.Open()
110 Dim ct As String = "select ISNULL(sum(GrandTotal),0),ISNULL(sum(TotalPayment),0),ISNULL(sum(PaymentDue),0) from Stock,Supplier where Supplier.ID=Stock.SupplierID and Date between @d3 and @d4"
111 cmd = New SqlCommand(ct)
112 cmd.Parameters.Add("@d3", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
113 cmd.Parameters.Add("@d4", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
114 cmd.Connection = con
115 rdr = cmd.ExecuteReader()
116 While rdr.Read()
117 a = rdr.GetValue(0)
118 b = rdr.GetValue(1)
119 c = rdr.GetValue(2)
120 End While
121 con.Close()
122 con = New SqlConnection(cs)
123 con.Open()
124 cmd = New SqlCommand("SELECT CONVERT(varchar(10),YEAR(Date)) AS Year, SUM(GrandTotal) AS GrandTotal FROM Stock where date between @d3 and @d4 GROUP BY YEAR(Date) ORDER BY Year", con)
125 cmd.Parameters.Add("@d3", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
126 cmd.Parameters.Add("@d4", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
127 adp = New SqlDataAdapter(cmd)
128 dtable = New DataTable()
129 adp.Fill(dtable)
130 con.Close()
131 myDS.Tables.Add(dtable)
132 myDS.WriteXmlSchema("TotalPurchase.xml")
133 rpt.Subreports(0).SetDataSource(myDS)
134 rpt.SetDataSource(myDS)
135 rpt.SetParameterValue("p1", dtpDateFrom.Value.Date)
136 rpt.SetParameterValue("p2", dtpDateTo.Value.Date)
137 rpt.SetParameterValue("p3", a)
138 rpt.SetParameterValue("p4", b)
139 rpt.SetParameterValue("p5", c)
140 rpt.SetParameterValue("p6", Today)
141 frmReport.CrystalReportViewer1.ReportSource = rpt
142 frmReport.ShowDialog()
143 Catch ex As Exception
144 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
145 End Try
146 End Sub
147
148 Sub fillSupplier()
149 Try
150 con = New SqlConnection(cs)
151 con.Open()
152 adp = New SqlDataAdapter()
153 adp.SelectCommand = New SqlCommand("SELECT RTRIM(Name) FROM Supplier order by Name", con)
154 ds = New DataSet("ds")
155 adp.Fill(ds)
156 dtable = ds.Tables(0)
157 cmbSupplier.Items.Clear()
158 For Each drow As DataRow In dtable.Rows
159 cmbSupplier.Items.Add(drow(0).ToString())
160 Next
161 Catch ex As Exception
162 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
163 End Try
164 End Sub
165 Private Sub frmPurchaseReport_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
166 fillSupplier()
167 End Sub
168
169 Private Sub cmbCompany_Format(sender As System.Object, e As System.Windows.Forms.ListControlConvertEventArgs) Handles cmbSupplier.Format
170 If (e.DesiredType Is GetType(String)) Then
171 e.Value = e.Value.ToString.Trim
172 End If
173 End Sub
174 End Class
